package dyl.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class DbUtil {

	static {
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			//Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();//sqlserver
		} catch (Exception e) {
			System.out.println("oracle驱动加载失败");
		}
	}

	public static Connection openConnection(String dbstr) throws SQLException {
		if(dbstr.equals("sqlservcer_jw")){
			Properties props =new Properties();
			props.put("user","jw");
			props.put("password","Kingo163soft");
			return DriverManager.getConnection("jdbc:sqlserver://210.42.72.71:1433;databaseName=jhun_jwgl1db",props);
		}
		return null;
	}
	public static void closeConnection(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			System.out.println("oracle关闭失败");
		}
	}

	// jdbc:oracle:thin:@(description=(address=(host=host)(protocol=tcp)(port=port))(connect_data=(service_name=service_name)))
	public static void closeStatement(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			System.out.println("stm关闭失败");
		}
	}

	public static void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			System.out.println("rs关闭失败");
		}
	}

	public static void main(String[] args) {
		List list = getListInMap("select top 10 xh from T_XJ_StudBaseInfo", "sqlservcer_jw");
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
		System.out.println(list.size());
	}
	public static List getListInMap(String sql,String dbStr) {
		List list = new ArrayList();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int intRows = 0;
		try {
			con = DbUtil.openConnection(dbStr);
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			int intColNum = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				HashMap hm = new HashMap();
				intRows = intRows + 1;
				for (int i = 0; i < intColNum; i++) {
					if (rs.getString(i + 1) != null) {
						hm.put(rs.getMetaData().getColumnName(i + 1),
								rs.getString(i + 1));
					} else {
						hm.put(rs.getMetaData().getColumnName(i + 1), "");
					}
				}
				list.add(hm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(con);
		}
		return list;
	}
    public static Map getMapBySql(String sql,String dbStr) {
        HashMap hm = new HashMap();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int intRows = 0;
        try {
            con = DbUtil.openConnection(dbStr);
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            int intColNum = rs.getMetaData().getColumnCount();
            if (rs.next()) {
                intRows = intRows + 1;
                for (int i = 0; i < intColNum; i++) {
                    if (rs.getString(i + 1) != null) {
                        hm.put(rs.getMetaData().getColumnName(i + 1),
                                rs.getString(i + 1));
                    } else {
                        hm.put(rs.getMetaData().getColumnName(i + 1), "");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(con);
        }
        return hm;
    }
}
